package sqldbManagement.dbstudents;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import sqldbManagement.dbstudents.*;

public class StudentsDao {
	public String seeAll() throws SQLException {
		Connection conn = JdbcUtil.getConnection();
		//3.得到状态集
		String sql = "select uuid,name,gender,profession,code,birthday,age from student";	//给name起别名，则使用时必须使用别名,否则报错Column "name" not found
		java.sql.Statement st = conn.createStatement();
		ResultSet result=st.executeQuery(sql);
		try {
			while(result.next()) {							//当result下一条非空，循环使用get得到每一行中每一项，sout打印。
				String uuid=result.getString("uuid");
				String name5=result.getString("name");
				String gender=result.getString("gender");
				String profession=result.getString("profession");
				String code=result.getString("code");
				String birthday=result.getString("birthday");
				Integer age=result.getInt("age");
				System.out.printf("uuid:%s 姓名:%s 性别:%s 专业:%s 学号:%s 生日:%s 年龄:%d\n",uuid,name5,gender,profession,code,birthday,age);
			}
		}catch (Exception e) {
			System.out.println(e);
		}finally {
			try {
				//0.关闭
				if(result != null)
					result.close();
				if(st != null)
					st.close();
				if(conn != null)
					conn.close();
			}catch (Exception e2) {
				System.out.println(e2);
			}
		}
		//================================================================================================================================================
		return null;
	}
	public String getOneByNameDao(String name) throws SQLException {
		//================================================================================================================================================
		Connection conn = JdbcUtil.getConnection();
		//3.得到状态集
		java.sql.Statement st = conn.createStatement();
		String sql = "select uuid,name,gender,profession,code,birthday,age from student";	//给name起别名，则使用时必须使用别名,否则报错Column "name" not found
		ResultSet result=st.executeQuery(sql);
		try {
			while(result.next()) {							//当result下一条非空，循环使用get得到每一行中每一项，sout打印。
				String uuid=result.getString("uuid");
				String name5=result.getString("name");
				String gender=result.getString("gender");
				String profession1=result.getString("profession");
				String code=result.getString("code");
				String birthday=result.getString("birthday");
				Integer age=result.getInt("age");
				if(name5.equalsIgnoreCase(name))
				System.out.printf("uuid:%s 姓名:%s 性别:%s 专业:%s 学号:%s 生日:%s 年龄:%d\n",uuid,name5,gender,profession1,code,birthday,age);
			}
		}catch (Exception e) {
			System.out.println(e);
		}finally {
			try {
				//0.关闭
				if(result != null)
					result.close();
				if(st != null)
					st.close();
				if(conn != null)
					conn.close();
			}catch (Exception e2) {
				System.out.println(e2);
			}
		}
		//================================================================================================================================================
		return null;
	}
	public String getOneByProfessionDao(String profession) throws SQLException {
		//================================================================================================================================================
		Connection conn = JdbcUtil.getConnection();
		//3.得到状态集
		java.sql.Statement st = conn.createStatement();
		String sql = "select uuid,name,gender,profession,code,birthday,age from student";	//给name起别名，则使用时必须使用别名,否则报错Column "name" not found
		ResultSet result=st.executeQuery(sql);
		try {
			while(result.next()) {							//当result下一条非空，循环使用get得到每一行中每一项，sout打印。
				String uuid=result.getString("uuid");
				String name5=result.getString("name");
				String gender=result.getString("gender");
				String profession1=result.getString("profession");
				String code=result.getString("code");
				String birthday=result.getString("birthday");
				Integer age=result.getInt("age");
				if(profession1.equalsIgnoreCase(profession))
				System.out.printf("uuid:%s 姓名:%s 性别:%s 专业:%s 学号:%s 生日:%s 年龄:%d\n",uuid,name5,gender,profession1,code,birthday,age);
			}
		}catch (Exception e) {
			System.out.println(e);
		}finally {
			try {
				//0.关闭
				if(result != null)
					result.close();
				if(st != null)
					st.close();
				if(conn != null)
					conn.close();
			}catch (Exception e2) {
				System.out.println(e2);
			}
		}
		//================================================================================================================================================
		return null;
	}
	public Boolean isExistDao(String uuid) throws SQLException {
		//================================================================================================================================================
		//1.创建驱动
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		//2.获得链接
		String url = "jdbc:mysql://127.0.0.1:3306/db_students?useSSL=false&useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&serverTimezone=Hongkong&allowPublicKeyRetrieval=true";		//记牢,网络路径，参数
		Connection conn = DriverManager.getConnection(url, "root", "BaiXudemima26");
		//3.得到状态集
		java.sql.Statement st = conn.createStatement();
		String sql = "select uuid,name,gender,profession,code,birthday,age from student";	//给name起别名，则使用时必须使用别名,否则报错Column "name" not found
		ResultSet result=st.executeQuery(sql);
		try {
			while(result.next()) {							//当result下一条非空，循环使用get得到每一行中每一项，sout打印。
				String uuid1=result.getString("uuid");
				if(uuid1.equalsIgnoreCase(uuid)) {
					System.out.println("uuid已存在，请重新输入！");
					return true;
				}
					
			}
		}catch (Exception e) {
			System.out.println(e);
		}finally {
			try {
				//0.关闭
				if(result != null)
					result.close();
				if(st != null)
					st.close();
				if(conn != null)
					conn.close();
			}catch (Exception e2) {
				System.out.println(e2);
			}
		}
		//================================================================================================================================================
		return false;
	}
}
